package com.wondersgroup.fjzlpt.org.dao.impl.jtqy;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Repository;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.quick.framework.boot.repository.sql.bean.SQLBean;
import com.quick.framework.boot.repository.sql.bean.table.TableBean;
import com.quick.framework.boot.repository.sql.jdbcTemplate.impl.BaseJTDAOImpl;
import com.wondersgroup.fjzlpt.org.dao.inter.jtqy.FwbgxDAO;
@Repository
public class FwbgxDAOImpl extends BaseJTDAOImpl implements FwbgxDAO {
	@Override
	@Autowired
	@Qualifier("TB_JTQY_FWBGX")
	public void setTb(TableBean tb) {
		super.setTb(tb);
	}
	
    /**
     * 查询家庭签约服务包关系列表
     * @param
     * 
     */
	@Override
	public JSONArray queryList(Map<String, String> map) {
		JSONArray jsonArray = null;
		if(map!=null && map.size()>0){
			StringBuffer sql = new StringBuffer("select a.QYXXID,b.FWBID,b.FWBMC from TB_JTQY_FWBGX a, TB_XXWH_FWBJBXX b where 1=1 \n");
			sql.append(" and a.FWBID=b.FWBID ");
			List list = new ArrayList();
			if(!StringUtils.isEmpty((String)map.get("QYXXID")))
			{
				sql.append("and QYXXID=?");
				list.add(map.get("QYXXID"));
			}
			SQLBean sqlBean = new SQLBean(sql.toString(),list.toArray());
			jsonArray= super.queryList(sqlBean);
		}
		return jsonArray;
	}

	@Override
	public JSONObject queryOne(Map map) {
		JSONObject jsonObj = null;
		if(map!=null && map.size()>0){
			StringBuffer sql = new StringBuffer("select a.QYXXID,b.FWBID,b.FWBMC from TB_JTQY_FWBGX a, TB_XXWH_FWBJBXX b where 1=1 \n");
			sql.append(" and a.FWBID=b.FWBID ");
			List list = new ArrayList();
			if(!StringUtils.isEmpty((String)map.get("QYXXID")))
			{
				sql.append(" and QYXXID=?");
				list.add(map.get("QYXXID"));
			}
			SQLBean sqlBean = new SQLBean(sql.toString(),list.toArray());
			jsonObj = super.queryOne(sqlBean);
		}
		return jsonObj;
	}

	/**
	    * 查询服务包是否存在
	    * @param map
	    * @return
	    */
	public boolean validate(Map<String, String> map) {
		String fwbid = (String)map.get("FWBID");
		StringBuffer sql =  new StringBuffer();
		sql.append("select count(1) from TB_JTQY_FWBGX where FWBID = ? ");
		SQLBean sb = new SQLBean(sql.toString(), new Object[]{fwbid});
		return super.isExist(sb);
	}

	/**
	 * 批量删除
	 * @param map
	 */
	public void deleteBatch(Map<String, String> map){
		String QYXXID = map.get("QYXXID");
		if(!StringUtils.isEmpty(QYXXID)){
			StringBuffer sqlBuf = new StringBuffer();
			sqlBuf.append("delete ").append(this.tb.getTableName()).append(" where QYXXID=? ");

			SQLBean bean = new SQLBean(sqlBuf.toString(), QYXXID);
			super.update(bean);
		}
	}

	/**
	 * 查询签约服务项
	 * @param map
	 * @return
	 */
	@Override
	public JSONArray queryQyfwxByQyxx(Map<String,String> map){

		List sqlParams = new ArrayList();
		StringBuffer sqlBuf = new StringBuffer();
		sqlBuf.append("select a.QYXXID,b.FWBID,b.FWID from ").append(this.tb.getTableName()).append(" a \n");
		sqlBuf.append("left join TB_XXWH_FWB_FWXX b on a.FWBID=b.FWBID \n");
		sqlBuf.append("left join TB_XXWH_FWXX c on b.FWID=c.FWID where 1=1 \n");

		String value = map.get("QYXXID");
		if(StringUtils.isEmpty(value)){
			return null;
		}else{
			sqlBuf.append("and a.QYXXID = ? ");
			sqlParams.add(value);
		}

		if(!StringUtils.isEmpty(value = map.get("FWXBS"))){
			sqlBuf.append("and c.FWXBS = ? ");
			sqlParams.add(value);
		}


		SQLBean bean = new SQLBean(sqlBuf.toString(), sqlParams.toArray());
		return super.queryList(bean);
	}

	/**
	 * 查询服务包IDs
	 * @param QYXXID
	 */
	public JSONObject queryFwbidsByQyxxid(String QYXXID){
		StringBuffer sqlBuf = new StringBuffer();
		sqlBuf.append("SELECT LISTAGG(A.FWBID, ',') within group(order by null) FWBID,");
		sqlBuf.append("LISTAGG(A.FWBSX, ',') within group(order by null) FWBSX,sum(B.FY) ZFY \n");
		sqlBuf.append("FROM TB_JTQY_FWBGX A LEFT JOIN TB_XXWH_FWBJBXX B ON A.FWBID = B.FWBID ");
		sqlBuf.append("where qyxxid = ? GROUP BY A.QYXXID");
		SQLBean bean = new SQLBean(sqlBuf.toString(), QYXXID);
		return super.queryOne(bean);
	}

}


